Learning SQL Server: Select Query

Posted by Riino on

Menu

[TOC]

T-SQL : Query under Conditions

Main Frame

The basic searching code is under such format, A select --from --where is the most basic structure in a query.

select A,B,C,,Z
from R1,R2,...,Rn
where F

which can be transformed into :

πA1,A2,,An(σF(R1×R2××Rm))\pi_{A_1,A_2,\cdots,A_n}(\sigma_F(R_1\times R_2\times \cdots\times R_m))

Thus , select means π\pi , and where means σ\sigma​.

If there's no limitation , means you want to get all the information from single table :

select * from TABLENAME

here , * means no limitation. or you can replace from distinct , which means canceling same tuples.

behind where you can put bool calculation , and it will select with the answer is true.

All in all , the main structure of SELECT Query is :

SELECT select_list [INTO new_table]
[FROM table_source] [WHERE search_condition]
[GROUP BY group_by_expression]--Regroup the result by condition, each group generate a single tuple in result table.
[HAVING search_condition]
[ORDER BY order_expression[ASC | DESC]]

[Notice] A single select block can be nested for another select block.

Inner function

  • count([distinct|all] expression)

    Return the number of elements which is not null.

  • sum(expression)

Plus every elements together and return the result.

  • avg(expression)

    Return the average

  • max() and min()

Additional commend

//TODO

order by :